﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DTO_HealthInsurance;

namespace DAO_HealthInsurance
{
   public class DAOPolicyOnEmployee:ConnectDatabase
    {
        public SqlCommand Cmd;
        public DTOPoliciesOnEmployee objPoEmp;
        public DataTable ListPoEmp()
        {
            try
            {
                OpenConnect();
                Cmd = new SqlCommand("listPoEmp", Conn);
                Cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter da = new SqlDataAdapter(Cmd);
                DataTable dt = new DataTable("policiesonemployees");
                da.Fill(dt);
                CloseConnect();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public int InsertPoEmp(DTOPoliciesOnEmployee objPoEmp)
        {
            int Result = 0;
            try
            {
                OpenConnect();
                Cmd = new SqlCommand("InsertPoEmp", Conn);
                Cmd.CommandType = CommandType.StoredProcedure;
                Cmd.Parameters.Add("@EmpNo", SqlDbType.Int).Value = objPoEmp.EmpNo;
                Cmd.Parameters.Add("@PolicyId", SqlDbType.Int).Value = objPoEmp.PolicyId;
                Cmd.Parameters.Add("@PolicyName", SqlDbType.VarChar, 50).Value = objPoEmp.PolicyName;
                Cmd.Parameters.Add("@policyAmount", SqlDbType.Float).Value = objPoEmp.PolicyAmount;
                Cmd.Parameters.Add("@PolicyDuration", SqlDbType.Float).Value = objPoEmp.PolicyDuration;
                Cmd.Parameters.Add("@Emi", SqlDbType.Float).Value = objPoEmp.Emi;
                Cmd.Parameters.Add("@PStartDate", SqlDbType.DateTime).Value = objPoEmp.PolicyStartDate;
                Cmd.Parameters.Add("@PendDate", SqlDbType.DateTime).Value = objPoEmp.PolicyEndDate;
                Cmd.Parameters.Add("@CompanyId", SqlDbType.VarChar, 30).Value = objPoEmp.CompanyId;
                Cmd.Parameters.Add("@CompanyName", SqlDbType.VarChar, 50).Value = objPoEmp.CompanyName;
                Cmd.Parameters.Add("@Medical", SqlDbType.VarChar, 50).Value = objPoEmp.Medical;
                
                Result = Cmd.ExecuteNonQuery();
                CloseConnect();
            }
            catch (Exception ex)
            {

                throw ex;
            }
            return Result;
        }

        public int DeletePoEmp(DTOPoliciesOnEmployee objPoEmp)
        {
            int Result = 0;
            try
            {
                OpenConnect();
                Cmd = new SqlCommand("DeletePoEmp", Conn);
                Cmd.CommandType = CommandType.StoredProcedure;
                Cmd.Parameters.Add("@EmpNo", SqlDbType.Int).Value = objPoEmp.EmpNo;
                Result = Cmd.ExecuteNonQuery();
                CloseConnect();
            }
            catch (Exception ex)
            {

                throw ex;
            }
            return Result;

        }
        public int UpdatePoEmp(DTOPoliciesOnEmployee objPoEmp)
        {
            int Result = 0;
            try
            {
                OpenConnect();
                Cmd = new SqlCommand("UpdatePoEmp", Conn);
                Cmd.CommandType = CommandType.StoredProcedure;
                Cmd.Parameters.Add("@EmpNo", SqlDbType.Int).Value = objPoEmp.EmpNo;
                Cmd.Parameters.Add("@PolicyId", SqlDbType.Int).Value = objPoEmp.PolicyId;
                Cmd.Parameters.Add("@PolicyName", SqlDbType.VarChar, 50).Value = objPoEmp.PolicyName;
                Cmd.Parameters.Add("@policyAmount", SqlDbType.Float).Value = objPoEmp.PolicyAmount;
                Cmd.Parameters.Add("@PolicyDuration", SqlDbType.Float).Value = objPoEmp.PolicyDuration;
                Cmd.Parameters.Add("@Emi", SqlDbType.Float).Value = objPoEmp.Emi;
                Cmd.Parameters.Add("@PStartDate", SqlDbType.DateTime).Value = objPoEmp.PolicyStartDate;
                Cmd.Parameters.Add("@PendDate", SqlDbType.DateTime).Value = objPoEmp.PolicyEndDate;
                Cmd.Parameters.Add("@CompanyId", SqlDbType.VarChar, 30).Value = objPoEmp.CompanyId;
                Cmd.Parameters.Add("@CompanyName", SqlDbType.VarChar, 50).Value = objPoEmp.CompanyName;
                Cmd.Parameters.Add("@Medical", SqlDbType.VarChar, 50).Value = objPoEmp.Medical;
                Result = Cmd.ExecuteNonQuery();
                CloseConnect();
            }
            catch (Exception ex)
            {

                throw ex;
            }
            return Result;

        }

        public DataTable GetPoEmpByEmpNo(DTOPoliciesOnEmployee objPoEmp)
        {
            try
            {
                Cmd = new SqlCommand("GetPoEmpByEmpNo", Conn);
                Cmd.CommandType = CommandType.StoredProcedure;
                Cmd.Parameters.Add("@EmpNo", SqlDbType.Int).Value = objPoEmp.EmpNo;
                SqlDataAdapter da = new SqlDataAdapter(Cmd);
                DataTable dt = new DataTable("policiesonemployees");
                da.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
    }
}
